package Server.BusinessObjects;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import Server.DataAccess.ConnectJDBCMySQL;

public class Question {

	private int id;
	private String name;
	private String option1;
	private String option2;
	private String option3;
	private String option4;
	private int rightOptionNumber;
	private int severity;

	public Question() {

	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getOption1() {
		return option1;
	}

	public void setOption1(String answer1) {
		this.option1 = answer1;
	}

	public String getOption2() {
		return option2;
	}

	public void setOption2(String answer2) {
		this.option2 = answer2;
	}

	public String getOption3() {
		return option3;
	}

	public void setOption3(String answer3) {
		this.option3 = answer3;
	}

	public String getOption4() {
		return option4;
	}

	public void setOption4(String answer4) {
		this.option4 = answer4;
	}

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public int getRightOptionNumber() {
		return rightOptionNumber;
	}

	public void setRightOptionNumber(int rightOptionNumber) {
		this.rightOptionNumber = rightOptionNumber;
	}

	public int getSeverity() {
		return severity;
	}

	public void setSeverity(int severity) {
		this.severity = severity;
	}

	public String getRightOptionText() {
		switch (this.rightOptionNumber) {
		case 1:
			return this.option1;
		case 2:
			return this.option2;
		case 3:
			return this.option3;
		case 4:
			return this.option4;
		default:
			return "Right answer unknown";
		}
	}


	public Question(ResultSet rs) throws SQLException {
		this.setId(rs.getInt("Id"));
		this.setName(rs.getString("name"));
		this.setOption1(rs.getString("option1"));
		this.setOption2(rs.getString("option2"));
		this.setOption3(rs.getString("option3"));
		this.setOption4(rs.getString("option4"));
		this.setRightOptionNumber(rs.getInt("rightOptionNumber"));
		this.setSeverity(rs.getInt("severity"));

	}

	public static boolean existsInDb(String questionName) {

		ArrayList<Question> questions;
		try {
			questions = (ArrayList<Question>) Question.getAllQuestion();

			for (Question q : questions) {
				if (q.getName().equalsIgnoreCase(questionName)) {
					return true;
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return false;

	}

	public static boolean existsInCategory(int questionId, int categoryId) {

		ArrayList<Question> questions;
		try {
			questions = (ArrayList<Question>) Question
					.getQuestionsByCat(categoryId);

			for (Question q : questions) {
				if (questionId == q.getId()) {
					return true;
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return false;

	}

	private static List<Question> getList(PreparedStatement ps)
			throws SQLException {
		List<Question> output = new ArrayList<Question>();

		try {
			ResultSet rs = ps.executeQuery();

			while (rs.next())
				output.add(new Question(rs));

			rs.close();
		} finally {
			Connection con = ps.getConnection();
			if (ps != null)
				ps.close();

			if (con != null)
				con.close();

		}

		return output;
	}

	public static Question getQuestion(int id) throws SQLException {
		String query = "Select * from questions where Id = ?";
		PreparedStatement ps = ConnectJDBCMySQL.getConnection()
				.prepareStatement(query);
		ps.setInt(1, id);

		List<Question> list = Question.getList(ps);

		return list.size() == 1 ? list.get(0) : null;
	}

	public static List<Question> getAllQuestion() throws SQLException {
		String query = "Select * from questions";
		PreparedStatement ps = ConnectJDBCMySQL.getConnection()
				.prepareStatement(query);

		return Question.getList(ps);
	}

	public static List<Question> getQuestionsByCatAndSeverity(int cat, int sev)
			throws SQLException {
		String query = "SELECT questions.id, questions.name, questions.option1, "
				+ "questions.option2, questions.option3, questions.option4, "
				+ "questions.rightOptionNumber, questions.severity "
				+ "FROM questions INNER JOIN quecat ON quecat.queId = questions.id "
				+ "WHERE quecat.catId = ? AND questions.severity = ?";
		PreparedStatement ps = ConnectJDBCMySQL.getConnection()
				.prepareStatement(query);
		ps.setInt(1, cat);
		ps.setInt(2, sev);

		return Question.getList(ps);
	}

	public static List<Question> getQuestionsByCat(int cat) throws SQLException {
		String query = "SELECT questions.id, questions.name, questions.option1, "
				+ "questions.option2, questions.option3, questions.option4, "
				+ "questions.rightOptionNumber, questions.severity "
				+ "FROM questions INNER JOIN quecat ON quecat.queId = questions.id "
				+ "WHERE quecat.catId = ?";
		PreparedStatement ps = ConnectJDBCMySQL.getConnection()
				.prepareStatement(query);
		ps.setInt(1, cat);

		return Question.getList(ps);
	}
	
	public static List<Category> getCategoryToQuestion(int questionId) throws SQLException {
		Connection dbCon = ConnectJDBCMySQL.getConnection();

		String query = "Select * from categories c, quecat qc Where c.id = qc.catId and qc.queId=?";
		
		PreparedStatement ps = dbCon.prepareStatement(query);

		ps.setInt(1, questionId);
		
		List<Category> list = Category.getList(ps);

		return list;	
	}

	
	public static int insertQuestionInDb(int category, String name,
			String Option1, String Option2, String Option3, String Option4,
			int rightOptionNumber, int severity) throws SQLException {
		Connection dbCon = ConnectJDBCMySQL.getConnection();

		String query = "INSERT INTO questions (name, option1, option2, option3, option4, rightOptionNumber, severity) VALUES (?,?,?,?,?,?,?);";
		PreparedStatement ps = dbCon.prepareStatement(query);

		ps.setString(1, name);
		ps.setString(2, Option1);
		ps.setString(3, Option2);
		ps.setString(4, Option3);
		ps.setString(5, Option4);
		ps.setInt(6, rightOptionNumber);
		ps.setInt(7, severity);
		ps.execute();

		query = "SELECT LAST_INSERT_ID();";
		ps = dbCon.prepareStatement(query);
		ResultSet rs = ps.executeQuery();
		rs.next();
		int newId = rs.getInt(1);

		query = "INSERT INTO quecat (catId, queId) VALUES (?,?);";
		ps = dbCon.prepareStatement(query);
		ps.setInt(1, category);
		ps.setInt(2, newId);
		ps.execute();
		// autocommit is on by default
		dbCon.close();

		return newId;
	}

	public static void addQuestionToCategory(int categoryId, int questionId)
			throws SQLException {
		Connection dbCon = ConnectJDBCMySQL.getConnection();

		String query = "INSERT INTO quecat (catId, queId) VALUES (?,?);";
		PreparedStatement ps = dbCon.prepareStatement(query);
		ps.setInt(1, categoryId);
		ps.setInt(2, questionId);
		ps.execute();
		dbCon.close();
	}

	public static void deleteQuestionFromDb(int questionId) throws SQLException {

		Connection dbCon = ConnectJDBCMySQL.getConnection();

		String query = "DELETE FROM quecat WHERE queId=?;";
		PreparedStatement ps = dbCon.prepareStatement(query);
		ps.setInt(1, questionId);
		ps.execute();

		query = "DELETE FROM questions WHERE id=?;";
		ps = dbCon.prepareStatement(query);
		ps.setInt(1, questionId);
		ps.execute();

		dbCon.close();
	}

	public static void updateQuestionInDb(int questionId,
			int newSeverity, String newQuestionName, String newOption1,
			String newOption2, String newOption3, String newOption4,
			int newRightOptionNumber) throws SQLException {
		
		Question q = Question.getQuestion(questionId);
		
		if(newSeverity==0){
			newSeverity=q.getSeverity();
		}
		if(newRightOptionNumber==0){
			newRightOptionNumber=q.getRightOptionNumber();
		}
		if(newQuestionName.equalsIgnoreCase("null")){
			newQuestionName=q.getName();
		}
		if(newOption1.equalsIgnoreCase("null")){
			newOption1=q.getOption1();
		}
		if(newOption2.equalsIgnoreCase("null")){
			newOption2=q.getOption2();
		}
		if(newOption3.equalsIgnoreCase("null")){
			newOption3=q.getOption3();
		}
		if(newOption4.equalsIgnoreCase("null")){
			newOption4=q.getOption4();
		}
		
		Connection dbCon = ConnectJDBCMySQL.getConnection();

		String query = "UPDATE questions SET name=?, option1=?, option2=?, option3=?, option4=?, rightOptionNumber=?, severity=? WHERE id=?";
		PreparedStatement ps = dbCon.prepareStatement(query);
		ps.setString(1, newQuestionName);
		ps.setString(2, newOption1);
		ps.setString(3, newOption2);
		ps.setString(4, newOption3);
		ps.setString(5, newOption4);
		ps.setInt(6, newRightOptionNumber);
		ps.setInt(7, newSeverity);
		ps.setInt(8, questionId);
		ps.execute();

		dbCon.close();
	}

	public static void deleteQuestionFromCategory(int questionId, int categoryId) throws SQLException {
		Connection dbCon = ConnectJDBCMySQL.getConnection();

		String query = "DELETE FROM quecat WHERE queId=? AND catId=?;";
		PreparedStatement ps = dbCon.prepareStatement(query);
		ps.setInt(1, questionId);
		ps.setInt(2, categoryId);
		ps.execute();

		dbCon.close();
		
	}

}
